package com.guagua.chat.star;
/**
 * 北京八度互联科技有限公司版权所有
 * Copyright (C) Badu Corporation. All Rights Reserved
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.guagua.base.database.ConnectionManager;
import com.guagua.chat.base.BaseDao;
import com.guagua.chat.base.PaginationInfo;
import com.guagua.chat.room.ChatRoomUtil;
import com.guagua.vas.constants.ConfigConstants;


/** ******************************************************************
* 类概要:明星操作类，对表t_room_singer的操作
 * @author 白云鹏
 * @version 2012年7月31日10:11:39
*******************************************************************/

public class StarDao extends BaseDao{
	private final int nicknamelen = 8;
	/**
	 * 函数说明：根据房间id分页查询所有明星 <br/>
	 * 
	 * @param currentPage
	 *            类型:(int)；<br/> 当前查询的页码<br/>；
	 * @param pageSize
	 *            类型:(int)；<br/> 每页记录数<br/>；
	 * @param room_id
	 *            类型:(long)；<br/> 房间id<br/>；
	 * @return 返回结果说明. （Object[]）对象数值，包含查询页数据以及分页显示信息
	 */
	public Object[] listStar(int currentPage,int pageSize,long room_id){
		Object[] arr = new Object[2];
		List stars = new ArrayList();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
        String sql = "select id,room_id,user_id,face,url,introduce,nickname,create_time,update_time,sort,level,starjobid from t_room_singer  with(nolock) where room_id="+room_id ;
		
       PaginationInfo pi = this.getPagination(sql,currentPage,pageSize);
//       sql += " order by sort asc ,level desc";
       String jsonPageInfo = this.getPagingJsonStr(pi);
       String sql_ = this.genPagingSql(pi,sql,
//     String sql_ = this.genPagingSql(pi,sql," level desc," +
       		"(case when (sort is null or sort=0) then (select top 1  max(sort)+1 from t_room_singer with(nolock)) else sort end )" +
       		" asc ,level desc");
       try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql_);
			rs = pstmt.executeQuery();
			Map star = null;
			while(rs.next()) {
				star = new HashMap();
				star.put("id", rs.getLong("id"));
				star.put("room_id", rs.getLong("room_id"));
				star.put("user_id", rs.getLong("user_id"));
				star.put("face", rs.getLong("face"));
				star.put("url", rs.getString("url"));
				star.put("introduce", rs.getString("introduce"));
				star.put("nickname", rs.getString("nickname"));
				star.put("nicknamelen",  ChatRoomUtil.returnStrByLen(rs.getString("nickname"),nicknamelen));
				star.put("create_time", rs.getLong("create_time"));
				star.put("update_time", rs.getLong("update_time"));
				star.put("sort", rs.getInt("sort"));
				star.put("level", rs.getInt("level"));
				star.put("starJobId",  rs.getInt("starjobid"));
				stars.add(star);
			}
		} catch (Exception sqle) {
			sqle.printStackTrace();
			
		} finally {
			ConnectionManager.closeConnection(rs, pstmt, con);
		}
		arr[0] = stars;
		arr[1] = jsonPageInfo;
		return arr;
	}
	/**
	 * 函数说明：根据房间id查询所有明星 <br/>
	 * 
	 * @param room_id
	 *            类型:(long)；<br/> 房间id<br/>；
	 * @return 返回结果说明. （List）明星对象集合
	 */
	public List listStar(long room_id){
		
		List stars = new ArrayList();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
        String sql = "select s.id,s.room_id,s.user_id,s.face,s.url,s.introduce,s.nickname,s.create_time,s.update_time,s.sort,s.level,s.starjobid,p.photo_path,p.id as photoid" +
        		" from t_room_singer s  with(nolock) left join t_photo p  with(nolock) on s.face=p.id where s.room_id="+room_id+" order by  "+
        		//" s.level desc,"+
        		"(case when (s.sort is null or s.sort=0) then (select top 1  max(sort)+1 from t_room_singer  with(nolock)) else s.sort end ) asc ,s.level desc";
		
       try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			Map star = null;
			while(rs.next()) {
				star = new HashMap();
				star.put("id", rs.getLong("id"));
				star.put("room_id", rs.getLong("room_id"));
				star.put("user_id", rs.getLong("user_id"));
				star.put("face", rs.getLong("face"));
				star.put("url", rs.getString("url"));
				star.put("sort", rs.getInt("sort"));
				star.put("level", rs.getInt("level"));
				star.put("introduce", rs.getString("introduce"));
				star.put("nickname", rs.getString("nickname"));
				star.put("nicknamelen",  ChatRoomUtil.returnStrByLen(rs.getString("nickname"),nicknamelen));
				star.put("create_time", rs.getLong("create_time"));
				star.put("update_time", rs.getLong("update_time"));
				star.put("imgURL", rs.getString("photo_path")==null?ConfigConstants.IMG_DEFAULT:ConfigConstants.IMG_URL+rs.getString("photo_path"));
				star.put("photoid", rs.getLong("photoid"));
				star.put("starJobId",  rs.getInt("starjobid"));
				stars.add(star);
			}
		} catch (Exception sqle) {
			sqle.printStackTrace();
			
		} finally {
			ConnectionManager.closeConnection(rs, pstmt, con);
		}
		return stars;
	}
	/**
	 * 函数说明：给某房间添加推荐的明星 <br/>
	 * 作废
	 * 
	 * @param star
	 *            类型:(Star)；<br/> 明星对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean addStar(Star star){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="declare @time bigint set @time=dbo.fn_datetime_to_timestamp(getdate()) insert into t_room_singer(room_id,user_id,face,url,introduce,nickname,create_time,update_time,sort)values" +
					   "(?,?,?,?,?,?,@time,@time)";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, star.getRoom_id());
			pstmt.setLong(2, star.getUser_id());
			pstmt.setLong(3,star.getFace());
			pstmt.setString(4,star.getUrl());
			pstmt.setString(5,star.getIntroduce());
			pstmt.setString(6,star.getNickname());
			pstmt.setInt(7,0);

			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
		
	}
	/**
	 * 函数说明：删除某房间的明星 <br/>
	 * 
	 * @param star
	 *            类型:(Star)；<br/> 明星对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean delStar(Star star){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="delete from  t_room_singer where id=?";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, star.getId());

			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
		
	}
	
	/**
	 * 函数说明：删除某房间内的所有明星 <br/>
	 * 
	 * @param star
	 *            类型:(Star)；<br/> 明星对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean delStars(long roomid){
		Connection con = null;
		Statement stmt = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false); 
			stmt = con.createStatement();
			String sql1="update t_photo set status=3 where id in (select face from t_room_singer   with(nolock) where room_id="+roomid+")";
			String sql2 = " delete from  t_room_singer where room_id="+roomid;
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			
			stmt.executeBatch();    //执行批处理 
			con.commit(); 
		}catch(Exception sqle){
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}finally{
			ConnectionManager.close(stmt, con);
		}
		return true;
		
	}
	/**
	 * 函数说明：删除某房间内的所有明星,更新时间小于当前时间的。 <br/>
	 * 
	 * @param star
	 *            类型:(Star)；<br/> 明星对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean delStars(long roomid,long currTime){
		Connection con = null;
		Statement stmt = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false); 
			stmt = con.createStatement();
			String sql1="update t_photo set status=3 where id in (select face from t_room_singer   with(nolock) where room_id="+roomid+" and update_time <> " + currTime+")";
			String sql2 = " delete from  t_room_singer where room_id="+roomid +" and update_time <> " + currTime;
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			
			stmt.executeBatch();    //执行批处理 
			con.commit(); 
		}catch(Exception sqle){
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}finally{
			ConnectionManager.close(stmt, con);
		}
		return true;
		
	}
	
	/**
	 * 通过游标，给新添加进入数据库的明星进行重新排序。
	 * 作废
	 * @param roomid
	 * @return
	 */
	public boolean sortStars(long roomid){
			boolean flag=false;
			Connection con = null;
			PreparedStatement pstmt = null;
			try{
				StringBuilder sb = new StringBuilder();
				sb.append("  update t_room_singer set sort= ")
				.append("  (select num from (select ROW_NUMBER() over(order by level desc) num,*  ")
				.append("   from t_room_singer    with(nolock)")
				.append("   where room_id="+roomid+") a ")
				.append("   where a.id=t_room_singer.id) ")
				.append("   where room_id="+roomid+" ")
				.append(" and (sort=0 or sort is null) ");
				con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
				pstmt = con.prepareStatement(sb.toString());			
			    pstmt.execute();
			    flag=true;
			}catch(Exception sqle){
				sqle.printStackTrace();
			}finally{
				ConnectionManager.close(pstmt, con);
			}
			return flag;
		
	}
	
	/**
	 * 通过游标，给新添加进入数据库的明星进行重新排序。
	 * 作废
	 * @param roomid
	 * @return
	 */
	public boolean sortStars1(long roomid){
			boolean flag=false;
			Connection con = null;
			PreparedStatement pstmt = null;
			try{
				StringBuilder sb = new StringBuilder();
				sb.append(" declare @sort int ")
				.append(" declare @id bigint ")
				// --定义游标
				.append(" declare sortForLevel cursor for ")
				//--使用游标的对象(跟据需要填入select文) 
				.append(" select row_number() over(order by level desc) as row_number,id from t_room_singer where room_id="+roomid+" ")
				//--打开游标
				.append(" open sortForLevel ")
				// --将游标向下移1行，获取的数据放入之前定义的变量@sort中 
				.append(" fetch next from sortForLevel into @sort,@id ")
				//           --判断是否成功获取数据
				.append(" while @@fetch_status=0 ")
				.append(" begin ")
				.append(" update t_room_singer set sort=@sort ")
				//--进行相应处理(跟据需要填入SQL文) 
				.append(" where id=@id ")
				// --将游标向下移1行
				.append(" fetch next from sortForLevel into @sort,@id ")
				.append(" end ")
				// --关闭游标
				.append(" close sortForLevel ")
				.append(" deallocate sortForLevel ");
				con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
				pstmt = con.prepareStatement(sb.toString());			
			    pstmt.execute();
			    flag=true;
			}catch(Exception sqle){
				sqle.printStackTrace();
			}finally{
				ConnectionManager.close(pstmt, con);
			}
			return flag;
		
	}
	
	/**
	 * 添加明星
	 * @param stars
	 * @param currTime
	 * @return
	 */
	public boolean addStars(List<Star> stars,long currTime){
		Connection con = null;
		Statement stmt = null;
		boolean rollback = false;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false); 
			String sql= "";
			stmt = con.createStatement();
			int i=0;
			for (Star star: stars) {
				sql=" if exists(select 1 from t_room_singer   with(nolock) where user_id="+star.getUser_id()+" and room_id="+star.getRoom_id()+")" +
					" begin " +
					" update t_room_singer set room_id="+star.getRoom_id()+",user_id="+star.getUser_id()+",url='"+star.getUrl()+
					"',introduce='"+star.getIntroduce()+"',nickname='"+star.getNickname()+"',update_time="+currTime+",level="+star.getLevel()+
					",starjobid="+star.getStarJobId()+" where user_id="+star.getUser_id()+" and room_id="+star.getRoom_id()+" and starjobid="+star.getStarJobId()+
				   " end"+
				   " else "+
				   " begin"+
				   " insert into t_room_singer(room_id,user_id,url,introduce,nickname,create_time,update_time,sort,level,starjobid)values" +
				   " ("+star.getRoom_id()+","+star.getUser_id()+",'"+star.getUrl()+"','"+star.getIntroduce()+"','"+
				    star.getNickname()+"',"+currTime+","+currTime+","+star.getSort()+","+star.getLevel()+","+star.getStarJobId()+")"+
				   " end";
				stmt.addBatch(sql);
				if(i<4){
					String sql1 = "declare @tempsort"+i+" int " +
							" select @tempsort"+i+" = max(sort) from t_room_singer   with(nolock) "+
							" if(@tempsort"+i+"<4) "+
							" update t_room_singer set sort=@tempsort"+i+"+1  where user_id="+star.getUser_id()+" and room_id="+star.getRoom_id();
					stmt.addBatch(sql1);
					i++;
				}
			}
			
			try {
				stmt.executeBatch();    //执行批处理 
			} catch (SQLException e1) {
				rollback = true;
			}

			if (rollback) {
				con.rollback();
			} else {
				con.commit();
			}
		}catch(Exception sqle){
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}finally{
			ConnectionManager.close(stmt, con);
		}
		return !rollback;
	}
	/**
	 * 函数说明：获得标题名称 <br/>
	 * 
	 * @param room_id
	 *            类型:(long)；<br/> 房间ID<br/>；
	 * @return 返回结果说明. （String）返回标题名称
	 */
	public String getStarSub(long room_id){
		String star_subject="";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			String sql="select subject from t_room_recommend with(nolock) where room_id=?";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, room_id);
			
			rs = pstmt.executeQuery();
			while(rs.next()){
				star_subject=rs.getString("subject");
				
			}
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(rs, pstmt, con);
		}
		
		
		return star_subject;
	}
	/**
	 * 函数说明：添加标题名称 <br/>
	 * 作废
	 * @param room_id
	 *            类型:(long)；<br/> 房间ID<br/>；
	 * @param subject
	 *            类型:(String)；<br/> 添加的标题名称<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean addStarSub(long room_id,String subject){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="insert into t_room_recommend(room_id,subject,create_time)" +
					"values(?,?,dbo.fn_datetime_to_timestamp(getdate()))";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, room_id);
			pstmt.setString(2, subject);
			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
		
	}
	/**
	 * 函数说明：更新标题名称 <br/>
	 * 
	 * @param room_id
	 *            类型:(long)；<br/> 房间ID<br/>；
	 * @param subject
	 *            类型:(String)；<br/> 新的标题名称<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean updateStarSub(long room_id,String subject){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="update t_room_recommend set subject=?,create_time=dbo.fn_datetime_to_timestamp(getdate())" +
					" where room_id=?";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, subject);
			pstmt.setLong(2, room_id);
			
			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
	}
	
	/**
	 * 修改明星顺序
	 * @param starId 明星ID
	 * @param sort 明星顺序
	 * @return
	 */
	public boolean modStarSort(long starId,int sort){
		Connection con = null;
		Statement stmt = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false); 
			stmt = con.createStatement();
			String sql1="declare @sort int select @sort=sort from  t_room_singer   with(nolock) where id="+starId;
			String sql2="declare @id int select @id=id from  t_room_singer   with(nolock) where sort="+sort;
			String sql3="update t_room_singer set sort="+sort+" where id="+starId;
			String sql4="update t_room_singer set sort=@sort where id=@id";
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			stmt.addBatch(sql3);
			stmt.addBatch(sql4);
			
			stmt.executeBatch();    //执行批处理 
			con.commit(); 
		}catch(Exception sqle){
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}finally{
			ConnectionManager.close(stmt, con);
		}
		return true;
	}

}
